﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using MySql.Data;
using MySql.Data.MySqlClient;
using MySQLStudio.Common;
using MySQLStudio.Common.Data;
using System.Data;

namespace MySQLStudio.DataAccess
{
    public class MySqlDataAccess
    {
        private MySqlDataAccess()
        {

        }

        /// <summary>
        /// Executes data reader
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="sqlQuery"></param>
        /// <returns></returns>
        public static MySqlDataReader ExecuteDataReader(string connectionString, string sqlQuery)
        {
            MySqlConnection connection = null;
            MySqlDataReader reader = null;

            SecUtility.ValidateParameter(ref connectionString, true, true, false, int.MaxValue);

            try
            {
                connection = new MySqlConnection(connectionString);

                MySqlCommand command = new MySqlCommand(sqlQuery);
                command.Connection = connection;

                connection.Open();
                reader = command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (MySql.Data.MySqlClient.MySqlException)
            {
                throw;
            }
            catch (Exception ex)
            {
                throw new DataBaseAccessException(ErrorMessages.DataBaseAccess_failed_to_execute_reader, ex);
            }

            return reader;
        }

        /// <summary>
        /// Executes data reader
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="sqlQuery"></param>
        /// <returns></returns>
        public static MySqlDataReader ExecuteDataReader(string connectionString, string sqlQuery, params Parameter[] parameters)
        {
            MySqlConnection connection = null;
            MySqlDataReader reader = null;

            SecUtility.ValidateParameter(ref connectionString, true, true, false, int.MaxValue);

            try
            {
                connection = new MySqlConnection(connectionString);

                MySqlCommand command = new MySqlCommand(sqlQuery);
                command.Connection = connection;

                foreach (Parameter parameter in parameters)
                {
                    MySqlParameter prm = new MySqlParameter();
                    prm.ParameterName = parameter.ParameterName;
                    prm.DbType = parameter.DbType;
                    prm.Size = parameter.Size;
                    prm.Value = parameter.Value;
                    prm.Direction = parameter.Direction;

                    command.Parameters.Add(prm);
                }

                connection.Open();
                reader = command.ExecuteReader(CommandBehavior.CloseConnection);
            }           
            catch (Exception ex)
            {
                throw new DataBaseAccessException(ErrorMessages.DataBaseAccess_failed_to_execute_reader, ex);
            }

            return reader;
        }

        /// <summary>
        /// Executes query
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="sqlQuery"></param>        
        public static int ExecuteNonQuery(string connectionString, string sqlQuery)
        {
            MySqlConnection connection = null;
            int result;

            SecUtility.ValidateParameter(ref connectionString, true, true, false, int.MaxValue);

            try
            {
                connection = new MySqlConnection(connectionString);

                MySqlCommand command = new MySqlCommand(sqlQuery);
                command.Connection = connection;

                connection.Open();
                result = command.ExecuteNonQuery();
            }           
            catch (Exception ex)
            {
                throw new DataBaseAccessException(ErrorMessages.DataBaseAccess_failed_to_execute_non_query, ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
            }

            return result;
        }

        /// <summary>
        /// Executes query
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="sqlQuery"></param>        
        public static int ExecuteNonQuery(string connectionString, string sqlQuery, params Parameter[] parameters)
        {
            MySqlConnection connection = null;
            int result;

            SecUtility.ValidateParameter(ref connectionString, true, true, false, int.MaxValue);

            try
            {
                connection = new MySqlConnection(connectionString);

                MySqlCommand command = new MySqlCommand(sqlQuery);
                command.Connection = connection;

                foreach (Parameter parameter in parameters)
                {
                    MySqlParameter prm = new MySqlParameter();
                    prm.ParameterName = parameter.ParameterName;
                    prm.DbType = parameter.DbType;
                    prm.Size = parameter.Size;
                    prm.Value = parameter.Value;
                    prm.Direction = parameter.Direction;

                    command.Parameters.Add(prm);
                }

                connection.Open();
                result = command.ExecuteNonQuery();
            }            
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
            }

            return result;
        }

        /// <summary>
        /// Executes query
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="sqlQuery"></param>        
        public static int ExecuteNonQueryWithTransaction(string connectionString, string sqlQuery)
        {
            MySqlConnection connection = null;
            MySqlTransaction transaction = null;
            int result;

            SecUtility.ValidateParameter(ref connectionString, true, true, false, int.MaxValue);

            try
            {
                connection = new MySqlConnection(connectionString);
                connection.Open();
                transaction = connection.BeginTransaction();

                MySqlCommand command = new MySqlCommand(sqlQuery);
                command.Connection = connection;
                command.Transaction = transaction;                

                result = command.ExecuteNonQuery();
                transaction.Commit();
            }
            catch (Exception ex)
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }

                throw new DataBaseAccessException(ErrorMessages.DataBaseAccess_failed_to_execute_non_query, ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
            }

            return result;
        }

        /// <summary>
        /// Checks is connection to MySql server is available
        /// </summary>
        /// <param name="host"></param>
        /// <param name="username"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public static bool IsConnectionAvailable(string host, int postNumber, string username, string password)
        {
            bool connectionAvailable = false;
            MySqlConnection connection = null;

            try
            {
                connection = new MySqlConnection(GenerateConnectionString(host, postNumber, username, password));
                connection.Open();

                connectionAvailable = connection.Ping();
            }
            catch (Exception)
            {
                connectionAvailable = false;
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }

            return connectionAvailable;
        }

        private static string GenerateConnectionString(string host, string username, string password)
        {
            return string.Format("Data Source={0};Initial Catalog=INFORMATION_SCHEMA;User ID={1};Password={2};",
                                 host, username, password);
        }

        private static string GenerateConnectionString(string host, int portNumber, string username, string password)
        {
            return string.Format("Data Source={0};Port={1};Initial Catalog=INFORMATION_SCHEMA;User ID={2};Password={3};",
                                 host, portNumber, username, password);
        }
    }
}
